#! /bin/bash
last_month_date=$(date -d "-1 month" "+%Y-%m-01")
today=$(date -d "now" "+%Y-%m-%d")
if [[ $1 == "" ]]
then
yesterday=$(date -d "-1 day" "+%Y-%m-%d")
else
yesterday=$1
fi

hive -S -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.max.dynamic.partitions.pernode=10000;
set hive.exec.max.dynamic.partitions=100000;
set hive.exec.max.created.files=150000;
--hive压缩
set hive.exec.compress.intermediate=true;
set hive.exec.compress.output=true;
--写入时压缩生效
set hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
set hive.enforce.bucketing=true;
set hive.enforce.sorting=true;
set hive.optimize.bucketmapjoin = true;
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
--并行执行
set hive.exec.parallel=true;
set hive.exec.parallel.thread.number=8;
--矢量化查询
set hive.vectorized.execution.enabled=true;
--关联优化器
set hive.optimize.correlation=true;
--读取零拷贝
set hive.exec.orc.zerocopy=true;
insert overwrite itcast_dwd.customer_relationship_dwd partition(payment_time_year, payment_time_month, payment_time_day)
select
id,
customer_id ,
nvl( origin_type ,-1) as origin_type ,
payment_time ,
substr(payment_time,12,2) as payment_time_hour,
nvl(itcast_clazz_id,-1) as itcast_clazz_id ,
nvl(creator,-1) creator,
if( origin_type in ('NETSERVICE','PRESIGNUP'),1,0) origin_type_stat,
substr(payment_time,1,4) payment_time_year,
substr(payment_time,6,2) payment_time_month,
substr(payment_time,9,2) payment_time_day
from itcast_ods.customer_relationship
where deleted = 0 and customer_id IS NOT NULL and payment_state='PAID'
and start_time>="${last_month_date}";"